Re: [SQL] Indexing on a boolean field?

At 18:33 +0300 on 15/6/98, Tom Lane wrote:


> I want to be able to select out the elements of a table that have
> a TRUE value in a boolean field "flag".  (Basically the flag means
> the record hasn't been processed yet.)  I can do something like
>
>     SELECT * FROM table WHERE flag = 't';

I know I'm nitpicking, but I think "WHERE flag" is more elegant. I have an
aversion to comparing to boolean literals. Flag = 'true' is equivalent to
Flag, and Flag = 'false' is equivalent to NOT Flag in every language I know.

> I thought of creating an index on the flag field, but soon found that
> you can't do it in Postgres (there's no operator for index on boolean).
> In any case, I doubt that btree or hash indexes would work well with
> only two distinct data values.

I don't know that they won't work well. They will both probably reduce to
sequential scan on all the records with the same value - which is what you
need. I mean, as soon as you pick the bucket with all 'true' values and no
'false' values, you've made the saving you needed.

> Another possibility is to keep the not-yet-processed records in a
> separate table, but that seems pretty ugly as well; especially since
> I sometimes want to see both processed and unprocessed records.

Well, you could use a union to do that.

> Has anyone got some advice on how to approach this problem?

If you ask me, I'd just replace the boolean with a char flag. Less elegant,
perhaps (and would make your above query stay the way you wrote it...), but
since I don't think booleans take less space than a whole byte anyway, it's
as space-efficient as booleans, and it has its own index operator family.

In case you're worried about integrity, you can define a constraint on the
char field, making sure it doesn't enter anything except 'T' and 'F', or
't' and 'f', or whatever.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma